// ### 作业：建一个用户表（有姓名,年龄,性别,创建时间,修改时间）,使用模块加mysql模块,实现对数据的增删改查
// ###       修改时记得，把修改时间改成修改时的时间，能实现分页（选做）

// ###       实现分页,能实现男女个数和总数的统计,能统计出（小于18） 和 18-45 和 45岁以上的人数（可以的话一条sql,分组,如果不能行就写多个sql统计）




let mysql =require("mysql");
let conn=mysql.createConnection({  host: "127.0.0.1", user: "root", password: "root", port: "3306", database: "student"});
conn.connect();

let dateStr = new Date().toLocaleString().replace("/", "-").replace("/", "-")

//增
function add(){
            let insertsql = "INSERT into user (`name`,`age`,`sex`,`updatatime`) values ('aadas1',20,'男','" +dateStr + "')";
            conn.query(insertsql, (err, data) => {
                console.log(err);
                console.log(data);
            })
}


//删
function dele(){
            let deletesql="delete from user where id=3";
            conn.query(deletesql, (err, data) => {
                console.log(err);
                console.log(data);
            });
} 

//改
function updata(){
    let updatasql="update user set name='a10',updatatime='"+dateStr+"'where id=2";
    conn.query(updatasql, (err, data) => {
        console.log(err);
        console.log(data);
    });

}

//查
function seach(){
    let seachsql="select *from user";
    conn.query(seachsql, (err, data) => {
        console.log(err);
        console.log(data);
    });
}
//查 分页 (每页两个数据)   i 是第几页
function seach2(i){
    
    let seachsql2="select *from user limit "+((i-1)*2)+","+2;
    conn.query(seachsql2, (err, data) => {
        console.log(err);
        console.log(data);
    });
}
//查 男女数量总和小于18 和 18-45 和 45岁以上的人数
function seach3(){
    let seachsql3="SELECT count(1) AS '<18' FROM `user`  where age<18 ";
    let seachsql4="SELECT count(1) AS '>45' FROM `user`  where age>45 ";
    let seachsql5='SELECT count(1) AS "18~45" FROM `user`  where age>18 and age<45 ';
    let seachsql6='SELECT sex,count(1) as "数量",(SELECT COUNT(1) FROM `user`) as"总数" FROM `user` GROUP BY sex ;';
 

    conn.query(seachsql6, (err, data) => {
        console.log(err);
        console.log(data);
    });
}
seach3();

conn.end()